package cn.ug.activity.web.utils;

import cn.ug.activity.bean.ChannelBean;
import cn.ug.activity.bean.GiveOutAverageValueBean;
import cn.ug.activity.bean.GiveOutBean;
import cn.ug.activity.bean.GiveOutFutureValueBean;
import cn.ug.analyse.bean.response.ActiveMemberAnalyseBean;
import cn.ug.analyse.bean.response.MemberAnalyseBean;
import cn.ug.pay.bean.*;
import cn.ug.util.UF;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Serializable;
import java.util.List;

import static cn.ug.util.ConstantUtil.SLASH;

public class ExcelUtil {
    private static final Log LOGGER = LogFactory.getLog(ExcelUtil.class);

    public static String writeToExcel(List<Serializable> list, int type, String savePath, String accessKey, String secretKey, String bucket, String fileDomain) {
        String result = null;
        if (list != null && list.size() > 0) {
            HSSFWorkbook workBook = new HSSFWorkbook();
            HSSFSheet sheet = workBook.createSheet();
            HSSFDataFormat format = workBook.createDataFormat();
            HSSFCellStyle style = workBook.createCellStyle();
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            style.setWrapText(true);
            style.setBorderBottom((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);
            style.setBorderTop((short) 1);
            int rowNum = 0;
            File file = null;
            try {
                for (Serializable serialize : list) {
                    try {
                        if (serialize instanceof GiveOutFutureValueBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addFutureHeader(sheet, style);
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addFutureContent(sheet, style, format, (GiveOutFutureValueBean) serialize, rowNum);
                        } else if (serialize instanceof GiveOutAverageValueBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addAvgHeader(sheet, style);
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addAvgContent(sheet, style, format, (GiveOutAverageValueBean) serialize, rowNum, type);
                        } else if (serialize instanceof GiveOutBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addTotalHeader(sheet, style, (GiveOutBean) serialize);
                                ExcelUtil.addTotalHeader(sheet, style);
                                rowNum++;
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addTotalContent(sheet, style, format, (GiveOutBean) serialize, rowNum);
                        } else if (serialize instanceof TradeTotalAmountBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addTransactionTotalHeader(sheet, style, (TradeTotalAmountBean) serialize);
                                ExcelUtil.addTransactionTotalHeader(sheet, style);
                                rowNum++;
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addTransactionTotalContent(sheet, style, format, (TradeTotalAmountBean) serialize, rowNum);
                        } else if (serialize instanceof TradeFutureValueBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addTransactionFutureHeader(sheet, style);
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addTransactionFutureContent(sheet, style, format, (TradeFutureValueBean) serialize, rowNum);
                        } else if (serialize instanceof TradeAverageValueBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addTransactionAvgHeader(sheet, style);
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addTransactionAvgContent(sheet, style, format, (TradeAverageValueBean) serialize, rowNum);
                        } else if (serialize instanceof AreaTransactionBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addTransactionAreaHeader(sheet, style);
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addTransactionAreaContent(sheet, style, format, (AreaTransactionBean) serialize, rowNum);
                        } else if (serialize instanceof ChannelBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addChannelHeader(sheet, style);
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addChannelContent(sheet, style, format, (ChannelBean) serialize, rowNum);
                        } else if (serialize instanceof MemberAnalyseBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addNewUserHeader(sheet, style, list);
                                ExcelUtil.addNewUserHeader(sheet, style);
                                rowNum++;
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addNewUserContent(sheet, style, format, (MemberAnalyseBean) serialize, rowNum);
                        } else if (serialize instanceof ActiveMemberAnalyseBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addActicveUserHeader(sheet, style, list);
                                ExcelUtil.addActiveUserHeader(sheet, style);
                                rowNum++;
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addActicveUserContent(sheet, style, format, (ActiveMemberAnalyseBean) serialize, rowNum);
                        } else if (serialize instanceof OpenPositionTotalBean) {
                            if (type == -1) {
                                if (rowNum == 0) {
                                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                    ExcelUtil.addOpenPositionTotalHeader(sheet, style, (OpenPositionTotalBean) serialize);
                                    ExcelUtil.addOpenPositionTotalHeader(sheet, style);
                                    rowNum++;
                                }
                                style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                                ExcelUtil.addOpenPositionTotalContent(sheet, style, format, (OpenPositionTotalBean) serialize, rowNum);
                            } else if (type == 1) {
                                if (rowNum == 0) {
                                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                    ExcelUtil.addOpenPositionTotalHeader(sheet, style, (OpenPositionTotalBean) serialize);
                                    ExcelUtil.addMoneyPayHeader(sheet, style);
                                    rowNum++;
                                }
                                style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                                ExcelUtil.addOpenPositionTotalContent(sheet, style, format, (OpenPositionTotalBean) serialize, rowNum);
                            } else if (type == 2) {
                                if (rowNum == 0) {
                                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                    ExcelUtil.addOpenPositionTotalHeader(sheet, style, (OpenPositionTotalBean) serialize);
                                    ExcelUtil.addGoldPayHeader(sheet, style);
                                    rowNum++;
                                }
                                style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                                ExcelUtil.addOpenPositionTotalContent(sheet, style, format, (OpenPositionTotalBean) serialize, rowNum);
                            } else if (type == 3) {
                                if (rowNum == 0) {
                                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                    ExcelUtil.addOpenPositionTotalHeader(sheet, style, (OpenPositionTotalBean) serialize);
                                    ExcelUtil.addGoldRecordHeader(sheet, style);
                                    rowNum++;
                                }
                                style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                                ExcelUtil.addOpenPositionTotalContent(sheet, style, format, (OpenPositionTotalBean) serialize, rowNum);
                            } else if (type >= 5 || type <= 7) {
                                if (rowNum == 0) {
                                    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                    ExcelUtil.addOpenPositionTotalHeader(sheet, style, (OpenPositionTotalBean) serialize);
                                    ExcelUtil.addExchangeGoldRecordHeader(sheet, style, type);
                                    rowNum++;
                                }
                                style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                                ExcelUtil.addOpenPositionTotalContent(sheet, style, format, (OpenPositionTotalBean) serialize, rowNum);
                            }
                        } else if (serialize instanceof OpenPositionPastBean) {
                            if (rowNum == 0) {
                                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                                ExcelUtil.addOpenPositionPastHeader(sheet, style, (OpenPositionPastBean) serialize);
                                ExcelUtil.addOpenPositionPastHeader(sheet, style);
                                rowNum++;
                            }
                            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                            ExcelUtil.addOpenPositionPastContent(sheet, style, format, (OpenPositionPastBean) serialize, rowNum);
                        } else {
                            break;
                        }
                    } catch (Exception e) {
                        LOGGER.error(e.getMessage());
                        continue;
                    }
                    rowNum++;
                }

                File path = new File(savePath);
                if (!path.exists() && path.isDirectory()) {
                    try {
                        path.mkdir();
                    } catch (Exception e) {
                        LOGGER.error(e.getMessage());
                    }
                }
                String fileName = UF.getRandomUUID() + ".xls";
                String filePath = savePath + SLASH + fileName;
                file = new File(filePath);
                if (!file.exists()) {
                    try {
                        file.createNewFile();
                    } catch (IOException e) {
                        LOGGER.error(e.getMessage());
                    }
                }

                try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
                    workBook.write(outputStream);
                } catch (IOException e) {
                    LOGGER.error(e.getMessage());
                }
                return UploadUtil.upload(accessKey, secretKey, bucket, filePath, fileName, fileDomain);
            } finally {
                if (workBook != null) {
                    try {
                        workBook.close();
                    } catch (IOException e) {
                    }
                }
                if (file != null && file.exists()) {
                    file.delete();
                }
            }
        }

        return result;
    }

    private static void addFutureContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, GiveOutFutureValueBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送加息交易金额");

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getOneDayAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getSevenDayAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getThirtyDayAmount().doubleValue());
    }

    private static void addTransactionFutureContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, TradeFutureValueBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getOneDayAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getSevenDayAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getThirtyDayAmount().doubleValue());
    }

    private static void addAvgContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, GiveOutAverageValueBean bean, int rowNum, int type) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        String value = "";
        if (type == 1) {
            value = "赠送现金红包交易金额";
        } else if (type == 2) {
            value = "赠送加息交易金额";
        } else if (type == 3) {
            value = "赠送奖励交易金额";
        }
        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(value);

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getEveryoneAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getSingleAmount().doubleValue());
    }

    private static void addTransactionAvgContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, TradeAverageValueBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getEveryoneAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getSingleAmount().doubleValue());
    }

    private static void addTransactionAreaContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, AreaTransactionBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(rowNum);

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getProvince());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getUserProportion().doubleValue() + "%");

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getBalance().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalRegularlyAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getAvgMoney().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getAvgCurrentAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getAvgRegularlyAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getPeopleNum());
    }

    private static void addChannelContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, ChannelBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getCode());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getName());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getId());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getAddTimeString());
    }

    private static void addNewUserContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, MemberAnalyseBean bean, int rowNum) {
        int column = 0;
        int index = rowNum;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(index);

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getChannelName());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRegisterNumber());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getBindingBankCardNumber());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRechargeNumber());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTradeNumber());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getUv());
    }

    private static void addActicveUserContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, ActiveMemberAnalyseBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getDay());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getBuyNumber());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getSellerNumber());
    }

    private static void addOpenPositionTotalContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, OpenPositionTotalBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getValue().doubleValue());
    }

    private static void addOpenPositionPastContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, OpenPositionPastBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getMa1().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getMa5().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getMa22().doubleValue());
    }

    private static void addTotalContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, GiveOutBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRewardAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getCouponAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTicketAmount().doubleValue());
    }

    private static void addTransactionTotalContent(HSSFSheet sheet, HSSFCellStyle style, HSSFDataFormat format, TradeTotalAmountBean bean, int rowNum) {
        int column = 0;
        HSSFRow row = sheet.createRow(++rowNum);
        HSSFCell cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTime());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTradeAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRegularlyTradeAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTbillGram().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRechargeAmount().doubleValue());

        cell = row.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getWithdrawAmount().doubleValue());
    }

    private static void addFutureHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("指标类型");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送近1日到期总金额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送近7日到期总金额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送近30日到期总金额");
    }

    private static void addTransactionFutureHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("近1日到期总量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("近7日到期总量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("近30日到期总量");
    }

    private static void addAvgHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("指标类型");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送人均持有量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送单次购买量");
    }

    private static void addTransactionAvgHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("人均持有量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("单次购买量");
    }

    private static void addTransactionAreaHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("序号");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("地域分布");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("用户数地区占比");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("用户账户总余额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("存量总克重");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("人均购买金额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("人均持有待处理提单克重");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("人均持有回租中提单克重");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计用户");
    }

    private static void addChannelHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("渠道编号");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("渠道名称");

        sheet.setColumnWidth(column, 30 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("邀请码");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("开通时间");
    }

    private static void addNewUserHeader(HSSFSheet sheet, HSSFCellStyle style, List<Serializable> list) {
        int registerNum = 0;
        int bindCardNum = 0;
        int rechargeNum = 0;
        int tradeNum = 0;
        for (Serializable serializable : list) {
            MemberAnalyseBean bean = (MemberAnalyseBean) serializable;
            registerNum += bean.getRegisterNumber();
            bindCardNum += bean.getBindingBankCardNumber();
            rechargeNum += bean.getRechargeNumber();
            tradeNum += bean.getTradeNumber();
        }
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("");

        sheet.setColumnWidth(column, 30 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(registerNum);

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bindCardNum);

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(rechargeNum);

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(tradeNum);
    }

    private static void addActicveUserHeader(HSSFSheet sheet, HSSFCellStyle style, List<Serializable> list) {
        int payGoldNum = 0;
        int sellGoldNum = 0;
        for (Serializable serializable : list) {
            ActiveMemberAnalyseBean bean = (ActiveMemberAnalyseBean) serializable;
            payGoldNum += bean.getBuyNumber();
            sellGoldNum += bean.getSellerNumber();
        }
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(payGoldNum);

        sheet.setColumnWidth(column, 30 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(sellGoldNum);
    }

    private static void addOpenPositionTotalHeader(HSSFSheet sheet, HSSFCellStyle style, OpenPositionTotalBean bean) {

        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalValue().doubleValue());
    }

    private static void addOpenPositionPastHeader(HSSFSheet sheet, HSSFCellStyle style, OpenPositionPastBean bean) {

        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalMa1().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalMa5().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalMa22().doubleValue());
    }

    private static void addNewUserHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("序号");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("渠道名称");

        sheet.setColumnWidth(column, 30 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("注册用户");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("认证绑卡用户");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("充值用户");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("交易用户");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("UV");
    }

    private static void addActiveUserHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("买黄金活跃用户");

        sheet.setColumnWidth(column, 30 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("卖黄金活跃用户");
    }

    private static void addOpenPositionTotalHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("总建仓成本");
    }

    private static void addMoneyPayHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("交易总量（克）");
    }

    private static void addGoldPayHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 25 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("定期转流动金存量（克）");
    }

    private static void addGoldRecordHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("流动金存量（克）");
    }

    private static void addExchangeGoldRecordHeader(HSSFSheet sheet, HSSFCellStyle style, int type) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        if (type == 5) {
            cell.setCellValue("日产品到期客户总额");
        } else if (type == 6) {
            cell.setCellValue("月产品到期客户总额");
        } else if (type == 7) {
            cell.setCellValue("周产品到期客户总额");
        }
    }

    private static void addOpenPositionPastHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("MA1建仓平均成本");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("MA5建仓平均成本");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("MA22建仓平均成本");
    }

    private static void addTotalHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送奖励交易金额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送红包交易金额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("赠送加息交易金额");
    }

    private static void addTotalHeader(HSSFSheet sheet, HSSFCellStyle style, GiveOutBean bean) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalRewardAmount().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalCouponAmount().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTotalTicketAmount().doubleValue());
    }

    private static void addTransactionTotalHeader(HSSFSheet sheet, HSSFCellStyle style, TradeTotalAmountBean bean) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("累计");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getTradeTotalAmount().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRegularlyTradeTotalAmount().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("/");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getRechargeTotalAmount().doubleValue());

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue(bean.getWithdrawTotalAmount().doubleValue());
    }

    private static void addTransactionTotalHeader(HSSFSheet sheet, HSSFCellStyle style) {
        int column = 0;
        sheet.setColumnWidth(column, 25 * 256);
        HSSFRow header = sheet.createRow(1);
        HSSFCell cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日期");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("交易总量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("回租中交易存量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("用户持有黄金总量");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("日增充值金额");

        sheet.setColumnWidth(column, 20 * 256);
        cell = header.createCell(column++);
        cell.setCellStyle(style);
        cell.setCellValue("提现总额");
    }
}